﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using Booking.Models;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace BookingSite.Models
{
    public class Event
    {
        public int       id                 {set; get; }
        public int idPlace { set; get; }
        public int idHall { set; get; }
        public XDocument caption { set; get; }
        /*public DateTime start { set; get; }
        public DateTime end { set; get; }
        public DateTime endReservation { set; get; }
        public XmlData description { set; get; }
        public String mainPhoto { set; get; }
        public int maxCountTickets { set; get; }
        public XmlData priceInPlace { set; get; }
        public String priceInDescription { set; get; }
        */
        private const string TableName = "tEvent";

        /* 
         * <table>
         *  <element>
         *      <default></default>
         *      <language></language>
         *       <caption></caption>
         *  </item>
         * </element>
         */

        public bool create()
        {
            if (string.IsNullOrEmpty(caption.ToString())) throw new ArgumentException("please set caption");
            if (idPlace < 0) throw new ArgumentException("idPlace < 0");
            if (idHall < 0) throw new ArgumentException("idHall < 0");

            try
            {
                if (!ConnectionDb.IsOpened) ConnectionDb.OpenDb();
                var command = new SqlCommand(String.Format("INSERT INTO {0} (idPlace, idHall, caption) VALUES (@idPlace, @idHall, @caption)", TableName), ConnectionDb.Connection);
                command.Parameters.Add("@idPlace", SqlDbType.Int);
                command.Parameters["@idPlace"].Value = idPlace;

                command.Parameters.Add("@idHall", SqlDbType.Int);
                command.Parameters["@idHall"].Value = idHall;

                command.Parameters.Add("@caption", SqlDbType.Xml);
                command.Parameters["@caption"].Value = caption.ToString();

                command.ExecuteNonQuery();
            }
            catch
            {
                return false;
            }

            return true;
        }

        public static Event Load(int id)
        {
            Event someEvent = null;

            if (!ConnectionDb.IsOpened) ConnectionDb.OpenDb();
            var adapter = new SqlDataAdapter(String.Format("SELECT * FROM {0} WHERE id={1}", TableName, id), ConnectionDb.Connection);
            var ds = new DataSet();
            adapter.Fill(ds, TableName);
            if (ds.Tables[TableName].Rows.Count > 0)
            {
                someEvent = new Event();
                var dr = ds.Tables[TableName].Rows[0];
                someEvent.id = id;
                someEvent.idPlace = (int) dr["idPlace"];
                someEvent.idHall = (int) dr["idHall"];
                someEvent.caption = XDocument.Load(new StringReader(dr["caption"].ToString()), LoadOptions.None);
            }
            return someEvent;
        }

        public static List<Event> LoadAll()
        {
            var listEvent = new List<Event>();
            
            if (!ConnectionDb.IsOpened) ConnectionDb.OpenDb();
            var adapter = new SqlDataAdapter("SELECT id FROM " + TableName, ConnectionDb.Connection);
            var ds = new DataSet();
            adapter.Fill(ds, TableName);
            if (ds.Tables[TableName].Rows.Count > 0)
            {
                for (var i = 0; i < ds.Tables[TableName].Rows.Count; i++)
                {
                    var dr = ds.Tables[TableName].Rows[i];
                    var id = (int)dr["id"];
                    var someEvent = Load(id);
                    if (someEvent != null) { listEvent.Add(someEvent); }
                }
            }
            return listEvent;
        }
    }
}